﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web
Imports System.Collections

Public Class clsApostas

    Private BD_Apostas As String = ConfigurationSettings.AppSettings("ConnectionStringApostas")

#Region "Obter Dados"

    Public Function A_GET_Utilizadores() As DataTable

        Try

            Dim dt As New DataTable
            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_Utilizadores", cnn)

            cmd.CommandType = CommandType.StoredProcedure
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_VerificarUtilizador(ByVal UserName As String) As Boolean

        Try

            Dim dr As Boolean
            Dim cnn As New SqlConnection(BD_Apostas)

            Dim sql As String = "A_GET_VerificarUtilizador"
            Dim cmd As New SqlCommand(sql, cnn)
            cmd.Parameters.Add(New SqlParameter("UserName", UserName))
            cmd.CommandType = CommandType.StoredProcedure

            If cnn.State = ConnectionState.Closed Then

                cnn.Open()
            End If

            dr = cmd.ExecuteScalar()

            cnn.Close()

            Return dr

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_Utilizador(ByVal IDUtilizador As Integer) As SqlDataReader

        Try
            Dim dr As SqlDataReader = Nothing

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_Utilizador", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Return dr

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function A_GET_UtilizadorPorUserName(ByVal UserName As String) As SqlDataReader

        Try
            Dim dr As SqlDataReader = Nothing

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_VerificarUtilizador", cnn)
            cmd.Parameters.Add(New SqlParameter("UserName", UserName))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Return dr

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function A_GET_UtilizadorCampeonatoActivo(ByVal IDUtilizador As Integer) As SqlDataReader

        Try
            Dim dr As SqlDataReader = Nothing

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_UtilizadorCampeonatoActivo", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Return dr

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function A_GET_Campeonatos() As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_Campeonatos", cnn)

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_UtilizadorCampeonatos(ByVal IDUtilizador As Integer) As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_UtilizadorCampeonatos", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function A_GET_Jornadas() As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_Jornadas", cnn)

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_JornadaDescricao(ByVal IDJornada As Integer) As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_JornadaDescricao", cnn)
            cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

            'Dim dr As SqlDataReader = Nothing

            'Dim cnn As New SqlConnection(BD_Apostas)
            'Dim cmd As New SqlCommand("A_GET_JornadaDescricao", cnn)
            'cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))

            'cmd.CommandType = CommandType.StoredProcedure

            'If (cnn.State <> ConnectionState.Open) Then

            '    cnn.Open()
            'End If

            'dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            'Return dr

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_JornadaCampeonato(ByVal IDCampeonato As Integer, ByVal IDJornada As Integer) As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_JornadaCampeonato", cnn)
            cmd.Parameters.Add(New SqlParameter("IDCampeonato", IDCampeonato))
            cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Function A_GET_ApostasUtilizadorJornada(ByVal IDUtilizador As Integer,
                                                   ByVal IDJornada As Integer) As DataTable

        Try
            Dim dt As New DataTable

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_GET_ApostasUtilizadorJornada", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))
            cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))

            cmd.CommandType = CommandType.StoredProcedure

            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If
            cnn.Close()

            Return dt

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

#End Region

#Region "Inserir Dados"

    Public Function A_ADD_Utilizador(ByVal UserName As String,
                                     ByVal DisplayName As String,
                                     ByVal Email As String,
                                     ByVal Password As String) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_ADD_Utilizador", cnn)
            cmd.Parameters.Add(New SqlParameter("UserName", UserName))
            cmd.Parameters.Add(New SqlParameter("DisplayName", DisplayName))
            cmd.Parameters.Add(New SqlParameter("Email", Email))
            cmd.Parameters.Add(New SqlParameter("Password", Password))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

    Public Function A_ADD_Apostar(ByVal IDUtilizador As Integer,
                                  ByVal IDCampeonato As Integer,
                                  ByVal IDJornada As Integer,
                                  ByVal NumJogo As Integer,
                                  ByVal Aposta1 As Integer,
                                  ByVal Aposta2 As Integer) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_ADD_Apostar", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))
            cmd.Parameters.Add(New SqlParameter("IDCampeonato", IDCampeonato))
            cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))
            cmd.Parameters.Add(New SqlParameter("NumJogo", NumJogo))
            cmd.Parameters.Add(New SqlParameter("Aposta1", Aposta1))
            cmd.Parameters.Add(New SqlParameter("Aposta2", Aposta2))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

#End Region

#Region "Actualizar Dados"

    Public Function A_UPD_Utilizador(ByVal UserName As String,
                                    ByVal DisplayName As String,
                                    ByVal Email As String,
                                    ByVal Password As String,
                                    ByVal IDUtilizador As Integer) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_UPD_Utilizador", cnn)
            cmd.Parameters.Add(New SqlParameter("UserName", UserName))
            cmd.Parameters.Add(New SqlParameter("DisplayName", DisplayName))
            cmd.Parameters.Add(New SqlParameter("Email", Email))
            cmd.Parameters.Add(New SqlParameter("Password", Password))
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

    Public Function A_UPD_ActivarCampeonato(ByVal IDUtilizador As Integer, ByVal IDCampeonato As Integer) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_UPD_ActivarCampeonato", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))
            cmd.Parameters.Add(New SqlParameter("IDCampeonato", IDCampeonato))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

    Public Function A_UPD_Aposta(ByVal IDAposta As Integer,
                                 ByVal IDCampeonato As Integer,
                                 ByVal IDUtilizador As Integer,
                                 ByVal IDJornada As Integer,
                                 ByVal NumJogo As Integer,
                                 ByVal Aposta1 As Integer,
                                 ByVal Aposta2 As Integer) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_UPD_Aposta", cnn)
            cmd.Parameters.Add(New SqlParameter("IDAposta", IDAposta))
            cmd.Parameters.Add(New SqlParameter("IDCampeonato", IDCampeonato))
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))
            cmd.Parameters.Add(New SqlParameter("IDJornada", IDJornada))
            cmd.Parameters.Add(New SqlParameter("NumJogo", NumJogo))
            cmd.Parameters.Add(New SqlParameter("Aposta1", Aposta1))
            cmd.Parameters.Add(New SqlParameter("Aposta2", Aposta2))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

#End Region

#Region "Apagar Dados"

    Public Function A_DEL_Utilizador(ByVal IDUtilizador As Integer) As String

        Try

            Dim cnn As New SqlConnection(BD_Apostas)
            Dim cmd As New SqlCommand("A_DEL_Utilizador", cnn)
            cmd.Parameters.Add(New SqlParameter("IDUtilizador", IDUtilizador))

            cmd.CommandType = CommandType.StoredProcedure

            If (cnn.State <> ConnectionState.Open) Then

                cnn.Open()
            End If

            cmd.ExecuteNonQuery()
            cnn.Close()

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try

    End Function

#End Region

End Class
